<?php
/**
 * Created by PhpStorm.
 * User: Yieong
 * Date: 14-9-23
 * Time: 上午12:01
 */

class ModUser extends Model {

    private $table = 'tblUser';

    // 表名
    public function tableName(){
        return $this->table;
    }

    public static function model($className=__CLASS__){
        return parent::model($className);
    }

    public function getDbConnection(){
        return Yii::app()->dbAuth;
    }

    /**
     * 根据Account或Email字段查询一个用户
     *
     * @param $loginID  查询信息：账号名或者邮箱
     * @return mixed    如果找到则返回用户的信息
     */
    public function searchUser($searchId){
        $sql = "SELECT UserID,Account,UserName,Password,IsAdmin,HasAllGame,IsViewAll,AddBy
                FROM {$this->table} WHERE Status=1 AND Account=:SearchID";
        $args = array(':SearchID' => $searchId);

        $result = $this->getDbConnection()->getOne($sql, $args);

        return $result;
    }

    public function getUserById($userId){
        $sql = "SELECT UserID,Account,UserName,IsAdmin,HasAllGame
                FROM {$this->table} WHERE Status=1 AND UserID=:UserID";
        $args = array(':UserID' => $userId);
        $result = $this->getDbConnection()->getOne($sql, $args);
        return $result;
    }
    
    public function page($params){
    	$table = $this->table;
    	$where = ' WHERE 1';
    	$args = array();
    
        if(!empty($params['search'])){
            $where .= ' AND (UserName LIKE :Search OR Account LIKE :Search) ';
            $args[':Search'] = "%{$params['search']}%";
        }

        if(!empty($params['status'])){
            $where .= ' AND Status IN (:Status) ';
            $args[':Status'] = $params['status'];
        }
        
        if(!empty($params['AddBy'])){
        	$where .= ' AND tbluser.AddBy = :AddBy ';
        	$args[':AddBy'] = $params['AddBy'];
        }
    
    	$c_sql = "select count(1) as cnt from {$table} left JOIN tbluserrolemap on tbluserrolemap.UserID = tbluser.UserID {$where}";
    	$c_result = $this->getDbConnection()->query($c_sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    
    	$order  = self::getOrder($params['order']);
    	$limit  = PubFunc::getLimit($params['length'], $params['page']);
    	$fields = "tbluser.UserID,Account,UserName,Status,IsAdmin,tbluser.AddBy,tbluser.AddTime,tbluserrolemap.RoleID";
    	$sql = "SELECT {$fields} FROM {$table} left JOIN tbluserrolemap on tbluserrolemap.UserID = tbluser.UserID {$where} {$order} {$limit}";
    	$data = $this->getDbConnection()->query($sql, $args);
    	$result = array('count'=>$count,'data'=>$data);
    	return $result;
    }
    
    public static function getOrder($order){
    	if(PubFunc::checkEmpty($order, array(0, 1))) return '';
    	if(!in_array($order[1], array('asc', 'desc'))){
    		$sort = 'asc';
    	}else{
    		$sort = $order[1];
    	}
    	return " ORDER BY {$order[0]} {$sort} ";
    }

    public function select($params=array()){
        // 子查询，筛选用户的类型和系统ID
        $where = ' WHERE 1';
        $args = array();

        if(!empty($params['search'])){
            $where .= ' AND (UserName LIKE :Search OR Account LIKE :Search) ';
            $args[':Search'] = "%{$params['search']}%";
        }

        if(!empty($params['status'])){
            $where .= ' AND Status IN (:Status) ';
            $args[':Status'] = $params['status'];
        }

        $sql = "SELECT UserID,Account,UserName,Status,IsAdmin
                FROM {$this->table} {$where}";

        $result = $this->getDbConnection()->getAll($sql, $args);

        return $result;
    }

    public function getList(){
        $sql = "SELECT UserID, Account, UserName, Email FROM {$this->table} WHERE AccAdmin!=1 AND Status=1 ORDER BY Account";

        $result = $this->getDbConnection()->getAll($sql);

        return $result;
    }
    
    /**
     * 去重
     * @param number $id  主键id
     * @param unknown $field 检查是否重复的字段名
     * @param unknown $val 检查是否重复的值
     * @return number
     */
    public function repeat($id,$field,$val){
    	$sql = "SELECT count(1) as cnt FROM {$this->table} where UserID != {$id} and {$field} = '{$val}'";
    	$c_result = $this->getDbConnection()->getAll($sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    	return $count;
    }

} 